﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using ControlLogNamespace;
using ControlHorsetrackNamespace;
using System.Threading;
using Subgurim.Controles;


namespace ControlDbNamespace
{
    /// <summary>
    /// Summary description for ControlDB
    /// </summary>
    public class ControlDB
    {
        #region Properties
        string strConnectionString;
        #endregion

        #region Constructor
        public ControlDB()
        {
            strConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";
        }
        #endregion

        #region MetodosDB
        /// <summary>
        ///  Coteja contra la DB si el usuario existe, y devuelve un objeto con los datos de usuario
        /// </summary>
        /// <param name="strNombreUsuario">Nombre del Usuario</param>
        /// <param name="strPasswordUsuario">Password del Usuario</param>
        /// <returns>Objeto con los datos del usuario</returns>
        public Cliente loginUsuario(string strNombreUsuario, string strPasswordUsuario)
        {
            Cliente objUsuario;
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand("SP_Login_Usuario", sConn);


            try
            {

                sConn.ConnectionString = strConnectionString;


                sCommand.CommandType = CommandType.StoredProcedure;
                sCommand.Parameters.Add(new SqlParameter("@Usuario", SqlDbType.VarChar)).Value = strNombreUsuario;
                sCommand.Parameters.Add(new SqlParameter("@Pwd", SqlDbType.VarChar)).Value = strPasswordUsuario;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                ControlLog.writeLog("[loginUsuario] EXEC SP_Login_Usuario '" + strNombreUsuario + "','" + strPasswordUsuario + "'");

                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                if (sReader.Read())
                {
                    objUsuario = new Cliente();
                    objUsuario.intIdUsuario = Convert.ToInt32(sReader["idUsuario"].ToString());
                    objUsuario.strPassword = strPasswordUsuario;
                    objUsuario.strUsuario = strNombreUsuario;
                    Coordenada objPosicionCentral = new Coordenada(sReader["latitudCentral"].ToString(), sReader["longitudCentral"].ToString());
                    ControlLog.writeLog("[loginUsuario] idUsuario =" + objUsuario.intIdUsuario.ToString());
                    objUsuario.guardarPosicionCentral(objPosicionCentral);
                    ControlLog.writeLog("[loginUsuario] latitudCentral =" + objUsuario.obtenerPosicionCentral().strLatitud);
                    ControlLog.writeLog("[loginUsuario] longitudCentral =" + objUsuario.obtenerPosicionCentral().strLongitud);
                }
                else
                {
                    sConn.Close();
                    ControlLog.writeLog("[loginUsuario] Usuario no encontrado. Verifique la contraseña y/o el nombre de usuario ingresados");
                    throw new Exception("Usuario no encontrado. Verifique la contraseña y/o el nombre de usuario ingresados");
                }


                sConn.Close();


                return objUsuario;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #region Caballos
        /// <summary>
        /// Devuelve una lista de los caballos de un usuario
        /// </summary>
        /// <param name="intIdCliente">Id de DB del Cliente en la tabla Clientes_Horsejack</param>
        /// <returns>Devuelve la lista de objetos del tipo objCaballo</returns>
        public List<Caballo> obtenerDatosCaballo(int intIdCliente)
        {
            List<Caballo> lstCaballosUsuario = new List<Caballo>();

            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand("SP_Request_Nodes_Position", sConn);


            try
            {

                sConn.ConnectionString = strConnectionString;


                sCommand.CommandType = CommandType.StoredProcedure;
                sCommand.Parameters.Add(new SqlParameter("@idCliente", SqlDbType.Int)).Value = intIdCliente;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                ControlLog.writeLog("[obtenerDatosCaballo] EXEC SP_Request_Nodes_Position " + intIdCliente.ToString());

                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    Caballo objCaballo = new Caballo(sReader["Nombre"].ToString(), sReader["Latitud"].ToString(), sReader["Longitud"].ToString());
                    objCaballo.strIDCaballo = sReader["IdCaballo"].ToString();
                    ControlLog.writeLog("[obtenerDatosCaballo] Nombre =" + sReader["Nombre"].ToString() + " Latitud =" + sReader["Latitud"].ToString() + " Longitud =" + sReader["Longitud"].ToString());
                    lstCaballosUsuario.Add(objCaballo);
                }


                sConn.Close();
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerDatosCaballo] Excepcion DB =" + ex.Message);
                throw ex;
            }


            return lstCaballosUsuario;
        }
        #endregion

        #region Geofences
        /// <summary>
        /// Guarda las coordenadas de una arista del GeoFence perteneciente al poligono de referencia
        /// </summary>
        /// <param name="objCoordenadaPoligono">Coordenadas de la arista</param>
        /// <param name="stringIdPoligono">Identificacion del poligono DB</param>
        /// <returns></returns>
        public void guardarCoordenada(Coordenada objCoordenadaPoligono, string strIdPoligono)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            try
            {
                string strQuery = "INSERT INTO ItemsPoligono (IdPoligono, latPoligono, longPoligono) VALUES (@idPoligono,@latPoligono, @longPoligono)";
                sConn.ConnectionString = strConnectionString;
                sCommand.Connection = sConn;

                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = strQuery;
                sCommand.Parameters.Add(new SqlParameter("@idPoligono", SqlDbType.Int)).Value = Convert.ToInt32(strIdPoligono);
                sCommand.Parameters.Add(new SqlParameter("@latPoligono", SqlDbType.VarChar, 25)).Value = objCoordenadaPoligono.strLongitud;
                sCommand.Parameters.Add(new SqlParameter("@longPoligono", SqlDbType.VarChar, 25)).Value = objCoordenadaPoligono.strLatitud;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                ControlLog.writeLog("[guardarCoordenada] INSERT INTO ItemsPoligono (IdPoligono, latPoligono, longPoligono) VALUES (" + strIdPoligono + ",'" + objCoordenadaPoligono.strLongitud + "','" + objCoordenadaPoligono.strLatitud + "')");

                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();

                sConn.Close();
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[guardarCoordenada] Excepcion DB =" + ex.Message);
                throw ex;
            }

        }

        public string obtenerIdPoligonoCreado(string strIdGeoFence)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();
            string strIdPoligono = "";
            try
            {
                string strQuery = "SELECT idPoligono FROM Poligonos WHERE idGeoFence=@idGeoFence";
                sCommand.Parameters.Add(new SqlParameter("@idGeoFence", SqlDbType.VarChar, 60)).Value = strIdGeoFence;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                ControlLog.writeLog("[obtenerIdPoligonoCreado] SELECT idPoligono FROM Poligonos WHERE idGeoFence='" + strIdGeoFence + "'");
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;
                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = strQuery;
                sConn.Open();
                sReader = sCommand.ExecuteReader();

                if (sReader.HasRows)
                {
                    if (sReader.Read())
                    {
                        strIdPoligono = sReader[0].ToString();
                    }
                }
                else
                {
                    throw new Exception("No se genero el Id de poligono del poligono recien creado.");
                }


                sConn.Close();
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerIdPoligonoCreado] Excepcion DB =" + ex.Message);
                throw ex;
            }
            return strIdPoligono;
        }

        /// <summary>
        /// Crea un GeoFence y devuelve el ID de DB de creacion
        /// </summary>
        /// <param name="objGeoFence">Objeto GeoFence a persistir</param>
        /// <returns>Devuelve un string con el ID de DB del GeoFence persistido</returns>  
        public string crearGeoFence(GeoFence objGeoFence)
        {
            ControlDB objConnection = new ControlDB();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();
            string strIdPoligono = "";

            try
            {
                string strQuery = "INSERT INTO Poligonos (idCliente,descripcion,idGeoFence) VALUES (@idCliente,@descripcion,@idGeoFence)";
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;
                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = strQuery;

                sCommand.Parameters.Add(new SqlParameter("@idCliente", SqlDbType.Int)).Value = objGeoFence.intIdCliente;
                sCommand.Parameters.Add(new SqlParameter("@descripcion", SqlDbType.VarChar, 200)).Value = objGeoFence.strDescripcion;
                sCommand.Parameters.Add(new SqlParameter("@idGeoFence", SqlDbType.VarChar, 60)).Value = objGeoFence.strIdGeoFence;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


                ControlLog.writeLog("[crearGeoFence] INSERT INTO Poligonos (idCliente,descripcion,idGeoFence) VALUES (" + objGeoFence.intIdCliente.ToString() + ",'" + objGeoFence.strDescripcion + "','" + objGeoFence.strIdGeoFence + "') ");

                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();

                sReader.Close();
                sConn.Close();
                strIdPoligono = objConnection.obtenerIdPoligonoCreado(objGeoFence.strIdGeoFence);
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[crearGeoFence] Excepcion DB =" + ex.Message);
                throw ex;
            }

            return strIdPoligono;
        }


        public List<GeoFence> obtenerGeoFences(int intIdCliente)
        {
            List<GeoFence> lstGeoFences = new List<GeoFence>();
            ControlDB objConnection = new ControlDB();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();
            List<int> lstIdPoligonos = new List<int>();

            try
            {
                ControlLog.writeLog("[obtenerGeoFences] Averiguando identidad de poligonos del cliente...");
                lstIdPoligonos = objConnection.obtenerIdGeoFences(intIdCliente);
                if (lstIdPoligonos.Count() == 0)
                {
                    ControlLog.writeLog("[obtenerGeoFences] No hay poligonos para el cliente");
                    return lstGeoFences;
                }
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerGeoFences] Excepcion identificando poligonos del cliente: " + ex.Message);
                throw ex;
            }

            string strQuery = "SELECT ip.latPoligono,ip.longPoligono,p.IdCliente,p.Descripcion,p.IdGeoFence FROM ItemsPoligono ip with(nolock) INNER JOIN Poligonos p with(nolock) ON p.idPoligono = ip.IdPoligono WHERE p.idPoligono = @idPoligono";
            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;
            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = strQuery;

            sCommand.Parameters.Add(new SqlParameter("@idPoligono", SqlDbType.Int));
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            foreach (int intIdPoligono in lstIdPoligonos)
            {
                try
                {

                    sCommand.Parameters["@idPoligono"].Value = intIdPoligono;

                    ControlLog.writeLog("[obtenerGeoFences] SELECT ip.latPoligono,ip.longPoligono,p.IdCliente,p.Descripcion,p.IdGeoFence FROM ItemsPoligono ip with(nolock) INNER JOIN Poligonos p with(nolock) ON p.idPoligono = ip.IdPoligono WHERE p.idPoligono =" + intIdPoligono.ToString());

                    sConn.Open();

                    sCommand.Connection = sConn;
                    sReader = sCommand.ExecuteReader();
                    GeoFence objGeoFenceRead = new GeoFence();
                    if (sReader.HasRows == false)
                    {
                        ControlLog.writeLog("[obtenerGeoFences] No hay GeoFences para este cliente");
                        return lstGeoFences;
                    }
                    while (sReader.Read())
                    {

                        objGeoFenceRead.AddCoordinates(sReader[0].ToString(), sReader[1].ToString());
                        objGeoFenceRead.AddInfo(sReader[2].ToString(), sReader[3].ToString(), sReader[4].ToString());


                    }

                    sReader.Close();
                    lstGeoFences.Add(objGeoFenceRead);

                    ControlLog.writeLog("[obtenerGeoFences] GeoFence[" + objGeoFenceRead.strDescripcion + "::" + objGeoFenceRead.strIdGeoFence + "]: Aristas: " + objGeoFenceRead.objPolygon.points.Count.ToString());

                    sConn.Close();
                }
                catch (Exception ex)
                {
                    ControlLog.writeLog("[obtenerGeoFences] Excepcion DB =" + ex.Message);
                    throw ex;
                }
            }
            return lstGeoFences;

        }

        /// <summary>
        /// Devuelve una lista de IDs de Poligono pertenecientes al Cliente
        /// </summary>
        /// <param name="intIdCliente">Id de DB del Cliente</param>
        /// <returns>Lista de ints con los Ids de poligono de un cliente</returns>
        public List<int> obtenerIdGeoFences(int intIdCliente)
        {
            List<int> lstIdGeoFences = new List<int>();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            try
            {
                string strQuery = "SELECT idPoligono FROM Poligonos with(nolock) WHERE idcliente = @idCliente";
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;
                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = strQuery;

                sCommand.Parameters.Add(new SqlParameter("@idCliente", SqlDbType.Int)).Value = intIdCliente;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


                ControlLog.writeLog("[obtenerIdGeoFences] SELECT idPoligono FROM Poligonos with(nolock) WHERE idcliente =" + intIdCliente.ToString());

                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();

                while (sReader.Read())
                {
                    lstIdGeoFences.Add(Convert.ToInt32(sReader[0].ToString()));
                }


                ControlLog.writeLog("[obtenerIdGeoFences] Poligonos obtenidos para el cliente " + intIdCliente.ToString() + ": " + lstIdGeoFences.Count().ToString());

                sConn.Close();
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerIdGeoFences] Excepcion DB =" + ex.Message);
                throw ex;
            }

            return lstIdGeoFences;
        }

        public void borrarGeoFence(string strGeoFenceId)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            try
            {
                string strQuery = "DELETE FROM ITEMSPOLIGONO WHERE idPoligono = (SELECT idPoligono FROM POLIGONOS WHERE idGeoFence = @idGeoFence)";
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;
                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = strQuery;

                sCommand.Parameters.Add(new SqlParameter("@idGeoFence", SqlDbType.VarChar, 60)).Value = strGeoFenceId;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


                ControlLog.writeLog("[borrarGeoFence] DELETE FROM ITEMSPOLIGONO WHERE idPoligono = (SELECT idPoligono FROM POLIGONOS WHERE idGeoFence = " + strGeoFenceId + ") ");

                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();
                sReader.Close();

                ControlLog.writeLog("[borrarGeoFence] DELETE FROM POLIGONO WHERE idGeoFence = " + strGeoFenceId);

                strQuery = "DELETE FROM POLIGONOS WHERE idGeoFence = @idGeoFence";
                sCommand.CommandText = strQuery;
                sReader = sCommand.ExecuteReader();

                sConn.Close();
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[borrarGeoFence] Excepcion DB =" + ex.Message);
                throw ex;
            }

        }


        #endregion

        #endregion

        /**********************************************************************************/
        /***public objCaballo objBuscarCaballoPorIMEI(string strActIMEI)                   ***/
        /***Modify: FD 12-3-12                                                          ***/
        /**********************************************************************************/
        public Caballo objBuscarCaballoPorIMEI(string strActIMEI)
        {
            Caballo objCaballo = new Caballo();
            Equipo objEquipo = new Equipo();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            try
            {
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;

                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = "SELECT TOP 1 Caballos_HORSEJACK.idCaballo,Caballos_HORSEJACK.Nombre,Caballos_HORSEJACK.idCliente," +
                "Caballos_HORSEJACK.idEquipo,Caballos_HORSEJACK.Habilitado,Caballos_HORSEJACK.Latitud,Caballos_HORSEJACK.Longitud," +
                "Caballos_HORSEJACK.UltimaActualizacion, Caballos_HORSEJACK.idGenero,Caballos_HORSEJACK.idCategoriaCaballo," +
                "Caballos_HORSEJACK.PathFotoCaballo,Equipos_HORSEJACK.idEquipo,Equipos_HORSEJACK.SIM_CodArea,Equipos_HORSEJACK.Telefono," +
                "Equipos_HORSEJACK.FechaInstalacion,Equipos_HORSEJACK.Reparaciones,Equipos_HORSEJACK.idOperador,Equipos_HORSEJACK.IMEI," +
                "Operadores_HORSEJACK.descOperador " +
                " FROM Caballos_HORSEJACK,Equipos_HORSEJACK,Operadores_HORSEJACK " +
                " WHERE Caballos_HORSEJACK.idEquipo= Equipos_HORSEJACK.idEquipo AND Equipos_HORSEJACK.IMEI=@IMEI " +
                " AND Operadores_HORSEJACK.idOperador=Equipos_HORSEJACK.idOperador " +
                " AND Caballos_HORSEJACK.Habilitado = 1";

                sCommand.Parameters.Add(new SqlParameter("@IMEI", SqlDbType.VarChar, 20)).Value = strActIMEI;

                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                try
                {
                    sConn.Open();

                    sCommand.Connection = sConn;
                    sReader = sCommand.ExecuteReader();

                    ControlLog.writeLog("[objBuscarCaballoPorIMEI] SELECT TOP 1 Caballos_HORSEJACK.idCaballo,Caballos_HORSEJACK.Nombre,Caballos_HORSEJACK.idCliente," +
                                        "Caballos_HORSEJACK.idEquipo,Caballos_HORSEJACK.Habilitado,Caballos_HORSEJACK.Latitud,Caballos_HORSEJACK.Longitud," +
                                        "Caballos_HORSEJACK.UltimaActualizacion, Caballos_HORSEJACK.idGenero,Caballos_HORSEJACK.idCategoriaCaballo," +
                                        "Caballos_HORSEJACK.PathFotoCaballo,Equipos_HORSEJACK.idEquipo,Equipos_HORSEJACK.SIM_CodArea,Equipos_HORSEJACK.Telefono," +
                                        "Equipos_HORSEJACK.FechaInstalacion,Equipos_HORSEJACK.Reparaciones,Equipos_HORSEJACK.idOperador,Equipos_HORSEJACK.IMEI," +
                                        "Operadores_HORSEJACK.descOperador" +
                                        " FROM Caballos_HORSEJACK,Equipos_HORSEJACK,Operadores_HORSEJACK " +
                                        " WHERE Caballos_HORSEJACK.idEquipo= Equipos_HORSEJACK.idEquipo AND Equipos_HORSEJACK.IMEI=" + strActIMEI +
                                        " AND Operadores_HORSEJACK.idOperador=Equipos_HORSEJACK.idOperador " +
                                        " AND Caballos_HORSEJACK.Habilitado = 1"
                                        );

                    if (sReader.Read())
                    {
                        //     Read your data 
                        // SELECT TOP 1  Equipos_HORSEJACK.idOperador,Equipos_HORSEJACK.IMEI,Operadores_HORSEJACK.descOperadorFROM Caballos_HORSEJACK,Equipos_HORSEJACK,Operadores_HORSEJACK WHERE Caballos_HORSEJACK.idEquipo= Equipos_HORSEJACK.idEquipo AND Equipos_HORSEJACK.IMEI=354779033053340 AND Operadores_HORSEJACK.idOperador=Equipos_HORSEJACK.idOperador
                        //   ControlLog.writeLog("[objBuscarCaballoPorIMEI] strIDCaballo = " + sReader.GetValue(0).ToString());
                        objCaballo.strIDCaballo = sReader.GetValue(0).ToString();
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] strNombre " + sReader.GetValue(1).ToString());
                        objCaballo.strNombre = sReader.GetValue(1).ToString();
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] Habilitado " + sReader.GetValue(4).ToString());
                        objCaballo.Habilitado = Convert.ToBoolean(sReader.GetValue(4).ToString());
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] Latitud " + sReader.GetValue(5).ToString());
                        objCaballo.objPosicion.strLatitud = sReader.GetValue(5).ToString();
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] Longitud " + sReader.GetValue(6).ToString());
                        objCaballo.objPosicion.strLongitud = sReader.GetValue(6).ToString();
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] dtUltimaActualizacion " + sReader.GetValue(7).ToString());
                        objCaballo.dtUltimaActualizacion = Convert.ToDateTime(sReader.GetValue(7).ToString());

                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] strIDEquipo " + sReader.GetValue(11).ToString());
                        objEquipo.inIDEquipo = Convert.ToInt32(sReader.GetValue(11).ToString());      //Reparaciones
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] strSimCodArea " + sReader.GetValue(12).ToString());
                        objEquipo.strSimCodArea = sReader.GetValue(12).ToString();
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] strTelefono " + sReader.GetValue(13).ToString());
                        objEquipo.strTelefono = sReader.GetValue(13).ToString();                                    //Telefono
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] dtFechaInstalacion " + sReader.GetValue(14).ToString());
                        objEquipo.dtFechaInstalacion = Convert.ToDateTime(sReader.GetValue(14).ToString());         //FechaInstalacion
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] inCantidadDeReparaciones " + sReader.GetValue(15).ToString());
                        objEquipo.inCantidadDeReparaciones = Convert.ToInt32(sReader.GetValue(15).ToString());      //Reparaciones
                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] strIMEI " + sReader.GetValue(17).ToString());
                        objEquipo.strIMEI = sReader.GetValue(17).ToString();                                           //IMEI

                        //  ControlLog.writeLog("[objBuscarCaballoPorIMEI] Operador " + sReader.GetValue(16).ToString());
                        switch (sReader.GetValue(16).ToString())                                                     //Desc Operador
                        {
                            case "Nextel":
                                objEquipo.enumOperador = Operador.Nextel;
                                break;
                            case "Personal":
                                objEquipo.enumOperador = Operador.Personal;
                                break;
                            case "Claro":
                                objEquipo.enumOperador = Operador.Claro;
                                break;
                            case "Movistar":
                                objEquipo.enumOperador = Operador.Movistar;
                                break;
                            default:
                                objEquipo.enumOperador = Operador.OperadorDesconocido;
                                break;
                        }

                    }
                    else
                    {
                        throw new Exception("No se encontraron caballos con este IMEI: " + strActIMEI +" o el caballo / celado ESTA DESHABILITADO");
                    }

                }
                catch (Exception ex)
                {
                    ControlLog.writeLog("[objBuscarCaballoPorIMEI] Excepcion DB =" + ex.Message);
                    throw ex;

                }
                sConn.Close();

                return objCaballo;

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[Buscar objCaballo] Excepcion =" + ex.Message);
                throw ex;
            }
        }

        /*******************************************************************************************/
        /***public bool actualizarDBconDatosGPS(DatosGPS objActDatosGPS, string strActCaballo)   ***/
        /***Modify: FD 12-3-12                                                                   ***/
        /*******************************************************************************************/
        public bool actualizarCaballoconDatosGPS(DatosGPS objActDatosGPS, Caballo objCaballoDatos)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            try
            {
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;

                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = "UPDATE Caballos_HORSEJACK  SET Latitud=@Latitud, Longitud=@Longitud WHERE idCaballo=@idCaballo";

                ControlLog.writeLog("[actualizarCaballoconDatosGPS] UPDATE Caballos_HORSEJACK  SET Latitud='" + objActDatosGPS.dbLatitud.ToString() + "', Longitud='" + objActDatosGPS.dbLongitud.ToString() + "' WHERE idCaballo=" + objCaballoDatos.strIDCaballo);


                sCommand.Parameters.Add(new SqlParameter("@Latitud", SqlDbType.VarChar, 15)).Value = objActDatosGPS.dbLatitud;
                sCommand.Parameters.Add(new SqlParameter("@Longitud", SqlDbType.VarChar, 15)).Value = objActDatosGPS.dbLongitud;
                sCommand.Parameters.Add(new SqlParameter("@idCaballo", SqlDbType.VarChar, 20)).Value = objCaballoDatos.strIDCaballo;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


                try
                {
                    sConn.Open();

                    sCommand.Connection = sConn;
                    sReader = sCommand.ExecuteReader();


                    if (sReader.Read())
                    {
                        //     Read your data 
                    }

                }
                catch (Exception ex)
                {
                    ControlLog.writeLog("[actualizarCaballoconDatosGPS] Excepcion DB =" + ex.Message);
                    throw ex;

                }
                sConn.Close();

                return true;
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[actualizarCaballoconDatosGPS] Excepcion =" + ex.Message);
                throw ex;
            }
        }


        public bool AgregarCoordenadasATrazabilidadHistorica(DatosGPS objActDatosGPS, Caballo objCaballoDatos)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            try
            {
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;

                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = "INSERT INTO TrazabilidadHistorica (Latitud,Longitud,Fecha,IdCaballo) VALUES (@Latitud,@Longitud,GETDATE(),@idCaballo)";

                ControlLog.writeLog("[AgregarCoordenadasATrazabilidadHistorica] INSERT INTO TrazabilidadHistorica (Latitud,Longitud,Fecha,IdCaballo) VALUES (" + objActDatosGPS.dbLatitud.ToString() + "," + objActDatosGPS.dbLongitud.ToString() + ",GETDATE()," + objCaballoDatos.strIDCaballo + ")");


                sCommand.Parameters.Add(new SqlParameter("@Latitud", SqlDbType.VarChar, 15)).Value = objActDatosGPS.dbLatitud;
                sCommand.Parameters.Add(new SqlParameter("@Longitud", SqlDbType.VarChar, 15)).Value = objActDatosGPS.dbLongitud;
                sCommand.Parameters.Add(new SqlParameter("@idCaballo", SqlDbType.VarChar, 20)).Value = objCaballoDatos.strIDCaballo;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


                try
                {
                    sConn.Open();

                    sCommand.Connection = sConn;
                    sReader = sCommand.ExecuteReader();


                    if (sReader.Read())
                    {
                        //     Read your data 
                    }

                }
                catch (Exception ex)
                {
                    ControlLog.writeLog("[AgregarCoordenadasATrazabilidadHistorica] Excepcion DB =" + ex.Message);
                    throw ex;

                }
                sConn.Close();

                return true;
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[AgregarCoordenadasATrazabilidadHistorica] Excepcion =" + ex.Message);
                throw ex;
            }
        }

        /// <summary>
        /// lo q hace
        /// </summary>
        /// <param name="objActDatosGPS">param 1</param>
        /// <param name="objCaballoDatos">param 2</param>
        /// <returns>asdas</returns>
        public bool agregarCoordenadasATrazabilidad(DatosGPS objActDatosGPS, Caballo objCaballoDatos)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            try
            {
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;

                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = "INSERT INTO Trazabilidad (Latitud,Longitud,Fecha,IdCaballo) VALUES (@Latitud,@Longitud,GETDATE(),@idCaballo)";

                ControlLog.writeLog("[agregarCoordenadasATrazabilidad] INSERT INTO Trazabilidad (Latitud,Longitud,Fecha,IdCaballo) VALUES (" + objActDatosGPS.dbLatitud.ToString() + "," + objActDatosGPS.dbLongitud.ToString() + ",GETDATE()," + objCaballoDatos.strIDCaballo + ")");


                sCommand.Parameters.Add(new SqlParameter("@Latitud", SqlDbType.VarChar, 15)).Value = objActDatosGPS.dbLatitud;
                sCommand.Parameters.Add(new SqlParameter("@Longitud", SqlDbType.VarChar, 15)).Value = objActDatosGPS.dbLongitud;
                sCommand.Parameters.Add(new SqlParameter("@idCaballo", SqlDbType.VarChar, 20)).Value = objCaballoDatos.strIDCaballo;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


                try
                {
                    sConn.Open();

                    sCommand.Connection = sConn;
                    sReader = sCommand.ExecuteReader();


                    if (sReader.Read())
                    {
                        //     Read your data 
                    }

                }
                catch (Exception ex)
                {
                    ControlLog.writeLog("[agregarCoordenadasATrazabilidad] Excepcion DB =" + ex.Message);
                    throw ex;

                }
                sConn.Close();

                return true;
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[agregarCoordenadasATrazabilidad] Excepcion =" + ex.Message);
                throw ex;
            }
        }

        /// <summary>
        /// Obtiene el camino realizado por un caballo
        /// </summary>
        /// <param name="objCaballo">objCaballo a analizar el trazo realizado por el</param>
        /// <param name="intCantidadDePasos">Cantidad de pasos discretos que se evaluaran</param>
        /// <returns>Trazabilidad del caballo</returns>
        public Trazabilidad obtenerCaminoCaballo(Caballo objCaballo, int intCantidadDePasos)
        {
            Trazabilidad objCaminoRealizado = new Trazabilidad();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            //sCommand.CommandText = "SELECT TOP " + intCantidadDePasos.ToString() + " Latitud,Longitud,Fecha FROM Trazabilidad WHERE idCaballo=@idCaballo ORDER BY Fecha DESC ";
            sCommand.CommandText = "SELECT Latitud,Longitud,Fecha FROM Trazabilidad with(nolock) WHERE idCaballo=@idCaballo and DATEDIFF(mi,Fecha,GETDATE()) <= 60 ORDER BY Fecha DESC ";

            // ControlLog.writeLog("[obtenerCaminoCaballo] SELECT TOP " + intCantidadDePasos.ToString() + " Latitud,Longitud,Fecha FROM Trazabilidad with(nolock) WHERE idCaballo=" + objCaballo.strIDCaballo + " ORDER BY Fecha DESC ");
            ControlLog.writeLog("[obtenerCaminoCaballo] SELECT TOP " + intCantidadDePasos.ToString() + " Latitud,Longitud,Fecha FROM Trazabilidad with(nolock) WHERE idCaballo=" + objCaballo.strIDCaballo + " and DATEDIFF(mi,Fecha,GETDATE()) <= 60 ORDER BY Fecha DESC ");

            sCommand.Parameters.Add(new SqlParameter("@idCaballo", SqlDbType.VarChar, 20)).Value = objCaballo.strIDCaballo;
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    try
                    {
                        objCaminoRealizado.agregarCoordenadas(Convert.ToDouble(sReader.GetValue(0).ToString()), Convert.ToDouble(sReader.GetValue(1)));
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[agregarCoordenadasATrazabilidad] Excepcion  =" + ex.Message + " Valores: " + sReader.GetValue(0).ToString() + "-" + sReader.GetValue(1).ToString());
                    }
                }

                ControlLog.writeLog("[agregarCoordenadasATrazabilidad] Cantidad de pasos encontrados: " + objCaminoRealizado.obtenerCantidadDePasos());


            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[agregarCoordenadasATrazabilidad] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();

            return objCaminoRealizado;

        }

        public List<TipoEvento> obtenerTiposDeEventos()
        {
            List<TipoEvento> lstTipoEventos = new List<TipoEvento>();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "SELECT idEvento,descEvento,descMensaje FROM EVENTO";

            ControlLog.writeLog("[obtenerTiposDeEventos] SELECT idEvento,descEvento FROM EVENTO");

            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    try
                    {
                        TipoEvento objEvento = new TipoEvento(sReader.GetValue(1).ToString(), sReader.GetValue(2).ToString(), Convert.ToInt32(sReader.GetValue(0).ToString()));
                        lstTipoEventos.Add(objEvento);
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[obtenerTiposDeEventos] Excepcion  =" + ex.Message);
                    }
                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerTiposDeEventos] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
            return lstTipoEventos;
        }
        public void agregarEventoDisponible(Evento objEvento)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "INSERT INTO EVENTODISPONIBLE (idCaballo,idEvento,Fecha,Mensaje,Leido) " +
                                    " VALUES (@idCaballo,@idEvento,GETDATE(),@mensaje,@leido) ";

            ControlLog.writeLog("[agregarEventoDisponible] INSERT INTO EVENTODISPONIBLE (idCaballo,idEvento,Fecha,Mensaje,Leido) VALUES (" + objEvento.obtenerIdRemitente() + "," + objEvento.obtenerIdEvento() + ",GETDATE(),'" + objEvento.obtenerMensaje() + "',false)");

            sCommand.Parameters.Add("@idCaballo", SqlDbType.Int).Value = objEvento.obtenerIdRemitente();
            sCommand.Parameters.Add("@idEvento", SqlDbType.Int).Value = objEvento.obtenerIdEvento();
            sCommand.Parameters.Add("@mensaje", SqlDbType.VarChar).Value = objEvento.obtenerMensaje();
            sCommand.Parameters.Add("@leido", SqlDbType.Bit).Value = false;
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                if (sReader.Read())
                {

                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[agregarEventoDisponible] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
        }
        public List<Alerta> obtenerAlertasDisponibles()
        {
            List<Alerta> lstAlerta = new List<Alerta>();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "SELECT idEventoDisponible,Mensaje,ModoAlerta FROM EVENTODISPONIBLE WHERE Leido=0  ORDER by FECHA DESC";

            ControlLog.writeLog("[obtenerAlertasDisponibles] SELECT idEventoDisponible,Mensaje,ModoAlerta FROM EVENTODISPONIBLE WHERE Leido=0  ORDER by FECHA DESC");

            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    try
                    {
                        Alerta objAlerta = new Alerta();
                        objAlerta.setIdAlerta(sReader.GetValue(0).ToString());
                        objAlerta.setMensajeAlerta(sReader.GetValue(1).ToString());
                        objAlerta.setModoAlerta(sReader.GetValue(2).ToString());

                        lstAlerta.Add(objAlerta);
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[obtenerAlertasDisponibles] Excepcion  =" + ex.Message);
                    }
                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerAlertasDisponibles] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
            return lstAlerta;
        }
        public void limpiarAlertaDisponible(Alerta objAlertaEnviada)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "UPDATE EVENTODISPONIBLE SET leido=1 WHERE idEventoDisponible=@idEventoDisponible";


            ControlLog.writeLog("[limpiarAlertaDisponible] UPDATE EVENTODISPONIBLE SET leido=1 WHERE idEventoDisponible=" + objAlertaEnviada.getIdAlerta());

            sCommand.Parameters.Add("@idEventoDisponible", SqlDbType.Int).Value = objAlertaEnviada.getIdAlerta();
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                if (sReader.Read())
                {

                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[limpiarAlertaDisponible] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
        }
        public List<TipoEvento> obtenerEvaluacionesDelCaballo(Caballo objCaballoEvaluado)
        {
            List<TipoEvento> lstTipoEventos = new List<TipoEvento>();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "SELECT e.idEvento,e.descEvento,e.descMensaje FROM EVENTO e INNER JOIN CABALLOEVENTO ce ON e.idEvento = ce.idEvento WHERE ce.idCaballo=@idCaballo";

            ControlLog.writeLog("[obtenerEvaluacionesDelCaballo] SELECT e.idEvento,e.descEvento,e.descMensaje FROM EVENTO e INNER JOIN CABALLOEVENTO ce ON e.idEvento = ce.idEvento WHERE ce.idCaballo=@idCaballo");

            sCommand.Parameters.Add("@idCaballo", SqlDbType.Int).Value = objCaballoEvaluado.strIDCaballo;
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    try
                    {
                        TipoEvento objEvento = new TipoEvento(sReader.GetValue(1).ToString(), sReader.GetValue(2).ToString(), Convert.ToInt32(sReader.GetValue(0).ToString()));
                        lstTipoEventos.Add(objEvento);
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[obtenerTiposDeEventos] Excepcion  =" + ex.Message);
                    }
                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerTiposDeEventos] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
            return lstTipoEventos;
        }




        /// <summary>
        /// Limpia los datos de trazabilidad de la base de datos cada el tiempo determinado
        /// </summary>
        /// <returns>asdas</returns>
        public bool limpiarDatosTrazabilidadSQL(int intMesesParam)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            try
            {
                sCommand.Connection = sConn;
                sConn.ConnectionString = strConnectionString;

                DateTime dtFechaLT = DateTime.Today;
                DateTime dtFechaLTMesA = dtFechaLT.AddMonths((intMesesParam) * (-1));

                sCommand.CommandType = CommandType.Text;
                sCommand.CommandText = "DELETE FROM Trazabilidad WHERE Fecha < @Date";

                ControlLog.writeLog("[limpiarDatosTrazabilidadSQL] DELETE FROM Trazabilidad WHERE Fecha < " + dtFechaLTMesA.ToString());


                sCommand.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 16)).Value = dtFechaLTMesA;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


                try
                {
                    sConn.Open();

                    sCommand.Connection = sConn;
                    sReader = sCommand.ExecuteReader();

                }
                catch (Exception ex)
                {
                    ControlLog.writeLog("[limpiarDatosTrazabilidadSQL] Excepcion DB =" + ex.Message);
                    throw ex;

                }
                sConn.Close();

                return true;
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[limpiarDatosTrazabilidadSQL] Excepcion =" + ex.Message);
                throw ex;
            }
        }

        public Cliente obtenerDuenoDeCaballo(Caballo objCaballo)
        {
            Cliente objCliente = new Cliente();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "SELECT TOP 1 cl.idCliente,cl.TelefonoPrimario,cl.Mail FROM Caballos_HORSEJACK ca INNER JOIN Clientes_HORSEJACK cl ON cl.idCliente = ca.idCliente WHERE ca.idCaballo = @idCaballo";



            ControlLog.writeLog("[obtenerDuenoDeCaballo] SELECT TOP 1 cl.idCliente,cl.TelefonoPrimario,cl.Mail FROM Caballos_HORSEJACK ca INNER JOIN Clientes_HORSEJACK cl ON cl.idCliente = ca.idCliente WHERE ca.idCaballo = " + objCaballo.strIDCaballo);

            sCommand.Parameters.Add("@idCaballo", SqlDbType.Int).Value = objCaballo.strIDCaballo;
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                if (sReader.Read())
                {
                    try
                    {
                        objCliente.intIdUsuario = Convert.ToInt32(sReader.GetValue(0).ToString());
                        objCliente.setPrimaryPhone(sReader.GetValue(1).ToString());
                        objCliente.setMail(sReader.GetValue(2).ToString());
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[obtenerDuenoDeCaballo] Excepcion  =" + ex.Message);
                    }
                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerDuenoDeCaballo] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
            return objCliente;
        }

        public void actualizarFechaUltimaActualizacion(Caballo objCaballo)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "UPDATE Caballos_HORSEJACK SET UltimaActualizacion = GETDATE() WHERE idCaballo = @idCaballo ";



            ControlLog.writeLog("[actualizarFechaUltimaActualizacion] UPDATE Caballos_HORSEJACK SET UltimaActualizacion = GETDATE() WHERE idCaballo = " + objCaballo.strIDCaballo);

            sCommand.Parameters.Add("@idCaballo", SqlDbType.Int).Value = objCaballo.strIDCaballo;
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();




            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[actualizarFechaUltimaActualizacion] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
        }

        public List<GeoCheckPoint> obtenerGeoCheckPoint(int p)
        {
            List<GeoCheckPoint> lstGeoCheckPoints = new List<GeoCheckPoint>();
            ControlDB objConnection = new ControlDB();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();
            List<int> lstIdPoligonos = new List<int>();

            /*
            try
            {
                ControlLog.writeLog("[obtenerGeoFences] Averiguando identidad de poligonos del cliente...");
                lstIdPoligonos = objConnection.obtenerIdGeoFences(intIdCliente);
                if (lstIdPoligonos.Count() == 0)
                {
                    ControlLog.writeLog("[obtenerGeoFences] No hay poligonos para el cliente");
                    return lstGeoFences;
                }
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerGeoFences] Excepcion identificando poligonos del cliente: " + ex.Message);
                throw ex;
            }
            */

            /*
            string strQuery = "SELECT ip.latPoligono,ip.longPoligono,p.IdCliente,p.Descripcion,p.IdGeoFence FROM ItemsPoligono ip with(nolock) INNER JOIN Poligonos p with(nolock) ON p.idPoligono = ip.IdPoligono WHERE p.idPoligono = @idPoligono";
            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;
            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = strQuery;

            sCommand.Parameters.Add(new SqlParameter("@idPoligono", SqlDbType.Int));
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            foreach (int intIdPoligono in lstIdPoligonos)
            {
                try
                {

                    sCommand.Parameters["@idPoligono"].Value = intIdPoligono;

                    ControlLog.writeLog("[obtenerGeoFences] SELECT ip.latPoligono,ip.longPoligono,p.IdCliente,p.Descripcion,p.IdGeoFence FROM ItemsPoligono ip with(nolock) INNER JOIN Poligonos p with(nolock) ON p.idPoligono = ip.IdPoligono WHERE p.idPoligono =" + intIdPoligono.ToString());

                    sConn.Open();

                    sCommand.Connection = sConn;
                    sReader = sCommand.ExecuteReader();
                    GeoFence objGeoFenceRead = new GeoFence();
                    if (sReader.HasRows == false)
                    {
                        ControlLog.writeLog("[obtenerGeoFences] No hay GeoFences para este cliente");
                        return lstGeoFences;
                    }
                    while (sReader.Read())
                    {

                        objGeoFenceRead.addCoordinates(sReader[0].ToString(), sReader[1].ToString());
                        objGeoFenceRead.addInfo(sReader[2].ToString(), sReader[3].ToString(), sReader[4].ToString());


                    }

                    sReader.Close();
                    lstGeoFences.Add(objGeoFenceRead);

                    ControlLog.writeLog("[obtenerGeoFences] GeoFence[" + objGeoFenceRead.strDescripcion + "::" + objGeoFenceRead.strIdGeoFence + "]: Aristas: " + objGeoFenceRead.objPolygon.points.Count.ToString());

                    sConn.Close();
                }
                catch (Exception ex)
                {
                    ControlLog.writeLog("[obtenerGeoFences] Excepcion DB =" + ex.Message);
                    throw ex;
                }
            }
             * */
            return lstGeoCheckPoints;
        }

        public bool ExisteImei(string ImeiParam)
        {
            bool returnValue = false;
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "SELECT COUNT(IMEI) FROM Equipos_Horsejack WHERE IMEI='" + ImeiParam + "'";

            ControlLog.writeLog("[ExisteImei] SQL=" + sCommand.CommandText);


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                if (sReader.Read())
                {
                    ControlLog.writeLog("[ExisteImei] Count IMEI = " + sReader.GetValue(0).ToString());
                    returnValue = (sReader.GetValue(0).ToString() != "0");

                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[ExisteImei] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
            return returnValue;
        }

        public string obtenerUltimaActualizacionUsuario(Cliente objUsuario)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();
            string strReturn = "";

            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "SELECT TOP 1 Nombre, UltimaActualizacion from Caballos_HORSEJACK WHERE idCliente = @idCliente ORDER BY UltimaActualizacion DESC";



            ControlLog.writeLog("[obtenerUltimaActualizacionUsuario] SELECT TOP 1 UltimaActualizacion from Caballos_HORSEJACK WHERE idCliente = " + objUsuario.intIdUsuario.ToString() + " ORDER BY UltimaActualizacion DESC");

            sCommand.Parameters.Add("@idCliente", SqlDbType.Int).Value = objUsuario.intIdUsuario.ToString();
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                if (sReader.Read())
                {

                    strReturn = sReader.GetValue(1).ToString() + " de " + sReader.GetValue(0).ToString();
                    ControlLog.writeLog("[obtenerUltimaActualizacionUsuario] Ultima actualizacion = " + strReturn);
                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerUltimaActualizacionUsuario] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();
            return "Ultima actualización: " + strReturn;
        }

        /// <summary>
        /// Agregar Equipo en DB
        /// </summary>
        /// <param name="objEquipo">Equipo</param>
        /// <returns>int</returns>
        public int AgregarEquipoEnDB(Equipo objEquipo)
        {
            int id = 0;
            try
            {
                using (SqlConnection sConn = new SqlConnection())
                {
                    sConn.ConnectionString = strConnectionString;

                    using (SqlCommand sCommand = new SqlCommand())
                    {
                        sCommand.Connection = sConn;
                        sCommand.CommandType = CommandType.Text;
                        sCommand.CommandText = "INSERT INTO Equipos_HORSEJACK (SIM_CodArea,Telefono,FechaInstalacion,Reparaciones,idOperador,CodeID,CodeStatus,UnitNumber,UnitVersion,Bateria,OtraFuncionalidad,IMEI) VALUES (@SIM_Code,@Telefono,@FechaInstalacion,@Reparaciones,@idOperador,@CodeID,@CodeStatus,@UnitNumber,@UnitVersion,@Bateria,@OtraFuncionalidad,@IMEI)";

                        sCommand.Parameters.Add(new SqlParameter("@SIM_Code", SqlDbType.VarChar, 6)).Value = objEquipo.strSimCodArea;
                        sCommand.Parameters.Add(new SqlParameter("@Telefono", SqlDbType.VarChar, 15)).Value = objEquipo.strTelefono;
                        sCommand.Parameters.Add(new SqlParameter("@FechaInstalacion", SqlDbType.DateTime)).Value = objEquipo.dtFechaInstalacion;
                        sCommand.Parameters.Add(new SqlParameter("@Reparaciones", SqlDbType.Int, 4)).Value = objEquipo.inCantidadDeReparaciones;
                        sCommand.Parameters.Add(new SqlParameter("@idOperador", SqlDbType.Int, 4)).Value = objEquipo.enumOperador;
                        sCommand.Parameters.Add(new SqlParameter("@CodeID", SqlDbType.VarChar, 6)).Value = objEquipo.strCodeID; //?
                        sCommand.Parameters.Add(new SqlParameter("@CodeStatus", SqlDbType.VarChar, 3)).Value = objEquipo.strCodeStatus;
                        sCommand.Parameters.Add(new SqlParameter("@UnitNumber", SqlDbType.VarChar, 4)).Value = objEquipo.strUnitNumber;
                        sCommand.Parameters.Add(new SqlParameter("@UnitVersion", SqlDbType.VarChar, 3)).Value = objEquipo.strUnitVersion;
                        sCommand.Parameters.Add(new SqlParameter("@Bateria", SqlDbType.VarChar, 2)).Value = objEquipo.strNivelDeBateria;
                        sCommand.Parameters.Add(new SqlParameter("@OtraFuncionalidad", SqlDbType.VarChar, 2)).Value = objEquipo.strOtraFuncionalidad;
                        sCommand.Parameters.Add(new SqlParameter("@IMEI", SqlDbType.VarChar, 20)).Value = objEquipo.strIMEI;

                        sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                        ControlLog.writeLog("[AgregarEquipoEnDB] SQL Query = " + sCommand.CommandText);
                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[AgregarEquipoEnDB] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                        sCommand.CommandText = "SELECT IDENT_CURRENT('Equipos_HORSEJACK')";
                        sCommand.Parameters.Clear();

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                                if (sReader.Read())
                                {
                                    id = Convert.ToInt32(sReader[0].ToString());
                                }
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[AgregarEquipoEnDB] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                    }

                }
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[AgregarEquipoEnDB] Excepcion =" + ex.Message);
                throw ex;
            }

            return id;
        }


        /// <summary>   //FD 15-8-12
        /// Seleccionar Datos de los Equipos en DB
        /// </summary>
        /// <param name="objCaballo">objCaballo a analizar el trazo realizado por el</param>
        /// <param name="intCantidadDePasos">Cantidad de pasos discretos que se evaluaran</param>
        /// <returns>Trazabilidad del caballo</returns>
        public Equipo obtenerDatosEquiposEnDB(Equipo objEquipo)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;
            sCommand.CommandType = CommandType.Text;


            sCommand.CommandText = "SELECT * FROM Equipo";

            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            ControlLog.writeLog("[obtenerDatosEquiposEnDB] " + sCommand.CommandText);

            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();

                if (sReader.Read())
                {
                    //     Read your data 

                    //   ControlLog.writeLog("[objBuscarCaballoPorIMEI] strIDCaballo = " + sReader.GetValue(0).ToString());
                    objEquipo.inIDEquipo = Convert.ToInt32(sReader.GetValue(0).ToString());      //ID

                    objEquipo.strSimCodArea = sReader.GetValue(1).ToString();

                    objEquipo.strTelefono = sReader.GetValue(2).ToString();

                    objEquipo.dtFechaInstalacion = Convert.ToDateTime(sReader.GetValue(3).ToString());         //FechaInstalacion

                    objEquipo.inCantidadDeReparaciones = Convert.ToInt32(sReader.GetValue(4).ToString());      //Cantidad de reparaciones

                    //objEquipo.enumOperador = ;      //Operaddor    //Harcoded

                    objEquipo.strCodeID = sReader.GetValue(6).ToString();

                    objEquipo.strCodeStatus = sReader.GetValue(7).ToString();

                    objEquipo.strUnitNumber = sReader.GetValue(8).ToString();

                    objEquipo.strUnitVersion = sReader.GetValue(9).ToString();

                    objEquipo.strNivelDeBateria = sReader.GetValue(10).ToString();

                    objEquipo.strOtraFuncionalidad = sReader.GetValue(11).ToString();

                    objEquipo.strIMEI = sReader.GetValue(12).ToString();                                           //IMEI


                }
                else
                {
                    throw new Exception("No se encontraron Datos Equipos");
                }

            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[obtenerDatosEquiposEnDB] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();

            return objEquipo;
        }

        /// <summary>               //FD 14-8-12
        /// Agregar Cliente en DB
        /// </summary>
        /// <param name="objEquipo">Cliente</param>
        /// <returns>int</returns>
        public int AgregarClienteEnDB(Cliente objCliente)
        {
            int id = 0;
            try
            {
                using (SqlConnection sConn = new SqlConnection())
                {
                    sConn.ConnectionString = strConnectionString;

                    using (SqlCommand sCommand = new SqlCommand())
                    {
                        sCommand.Connection = sConn;
                        sCommand.CommandType = CommandType.Text;
                        sCommand.CommandText = "INSERT INTO Clientes_HORSEJACK (Usuario,Nombre,Apellido,Password,mail,LatitudCent,LongitudCent,TelefonoPrimario,ActivarPrimeraVez) VALUES (@Usuario,@Nombre,@Apellido,@Password,@mail,@LatitudCent,@LongitudCent,@TelefonoPrimario," + (objCliente.ActivaPorPrimeraVez == true ? "1" : "0") + ")";

                        sCommand.Parameters.Add(new SqlParameter("@Usuario", SqlDbType.VarChar, 50)).Value = objCliente.strUsuario;
                        sCommand.Parameters.Add(new SqlParameter("@Nombre", SqlDbType.VarChar, 50)).Value = objCliente.strNombre;
                        sCommand.Parameters.Add(new SqlParameter("@Apellido", SqlDbType.VarChar, 50)).Value = objCliente.strApelido;
                        sCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 10)).Value = objCliente.strPassword;
                        sCommand.Parameters.Add(new SqlParameter("@mail", SqlDbType.VarChar, 50)).Value = objCliente.strMail;
                        sCommand.Parameters.Add(new SqlParameter("@LatitudCent", SqlDbType.VarChar, 20)).Value = objCliente.strLatitudCent; //?
                        sCommand.Parameters.Add(new SqlParameter("@LongitudCent", SqlDbType.VarChar, 20)).Value = objCliente.strLongitudCent;
                        sCommand.Parameters.Add(new SqlParameter("@TelefonoPrimario", SqlDbType.VarChar, 30)).Value = objCliente.strPrimaryPhone;

                        sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                        ControlLog.writeLog("[AgregarClienteEnDB] SQL Query = " + sCommand.CommandText);

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[agregarClienteenDB] Excepcion DB 1 =" + ex.Message);
                            throw ex;

                        }


                        sCommand.CommandText = "SELECT IDENT_CURRENT('Clientes_HORSEJACK')";
                        sCommand.Parameters.Clear();

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                                if (sReader.Read())
                                {
                                    id = Convert.ToInt32(sReader[0].ToString());
                                }
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[agregarClienteenDB] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                    }

                }
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[agregarClienteenDB] Excepcion =" + ex.Message);
                throw ex;
            }

            return id;
        }



        /// <summary>
        /// Agregar Cliente en DB
        /// </summary>
        /// <param name="objEquipo">Cliente</param>
        /// <returns>bool</returns>
        public int AgregarCaballoEnDB(Caballo objCaballo)
        {
            int id = 0;
            try
            {
                using (SqlConnection sConn = new SqlConnection())
                {
                    sConn.ConnectionString = strConnectionString;

                    using (SqlCommand sCommand = new SqlCommand())
                    {
                        sCommand.Connection = sConn;
                        sCommand.CommandType = CommandType.Text;
                        sCommand.CommandText = "INSERT INTO Caballos_HORSEJACK (Nombre,idCliente,idEquipo,Habilitado,Latitud,Longitud,UltimaActualizacion,idGenero,idCategoriaCaballo) VALUES (@Nombre,@idCliente,@idEquipo,@Habilitado,@Latitud,@Longitud,@UltimaActualizacion,@idGenero,@idCategoriaCaballo)";


                        //sCommand.Parameters.Add(new SqlParameter("@idTrazabilidad", SqlDbType.Int, 4)).Value = objCaballo.objTrazabilidad.inIDTrazabilidad;
                        ControlLog.writeLog("[AgregarCaballoEnDB] Nombre = " + objCaballo.strNombre);
                        sCommand.Parameters.Add(new SqlParameter("@Nombre", SqlDbType.VarChar, 50)).Value = objCaballo.strNombre;
                        ControlLog.writeLog("[AgregarCaballoEnDB] idCliente = " + objCaballo.objCliente.intIdUsuario.ToString());
                        sCommand.Parameters.Add(new SqlParameter("@idCliente", SqlDbType.Int, 4)).Value = objCaballo.objCliente.intIdUsuario;
                        ControlLog.writeLog("[AgregarCaballoEnDB] idEquipo = " + objCaballo.objEquipoCaballo.inIDEquipo.ToString());
                        sCommand.Parameters.Add(new SqlParameter("@idEquipo", SqlDbType.Int, 4)).Value = objCaballo.objEquipoCaballo.inIDEquipo;
                        ControlLog.writeLog("[AgregarCaballoEnDB] Habilitado = " + objCaballo.Habilitado.ToString());
                        sCommand.Parameters.Add(new SqlParameter("@Habilitado", SqlDbType.Bit, 1)).Value = objCaballo.Habilitado;
                        ControlLog.writeLog("[AgregarCaballoEnDB] Latitud = " + objCaballo.objPosicion.strLatitud.ToString());
                        sCommand.Parameters.Add(new SqlParameter("@Latitud", SqlDbType.VarChar, 15)).Value = objCaballo.objPosicion.strLatitud;
                        ControlLog.writeLog("[AgregarCaballoEnDB] Longitud = " + objCaballo.objPosicion.strLongitud.ToString());
                        sCommand.Parameters.Add(new SqlParameter("@Longitud", SqlDbType.VarChar, 15)).Value = objCaballo.objPosicion.strLongitud;
                        ControlLog.writeLog("[AgregarCaballoEnDB] UltimaActualizacion = " + objCaballo.dtUltimaActualizacion.ToString());
                        sCommand.Parameters.Add(new SqlParameter("@UltimaActualizacion", SqlDbType.DateTime, 16)).Value = objCaballo.dtUltimaActualizacion;
                        ControlLog.writeLog("[AgregarCaballoEnDB] idGenero = " + objCaballo.getIDRaza().ToString());
                        sCommand.Parameters.Add(new SqlParameter("@idGenero", SqlDbType.Int, 4)).Value = objCaballo.getIDRaza();                       //ESTO DEBERIA SER UN ID con indice A LA TABLA GENERO
                        ControlLog.writeLog("[AgregarCaballoEnDB] idCategoriaCaballo = " + objCaballo.getIDCategoria().ToString());
                        sCommand.Parameters.Add(new SqlParameter("@idCategoriaCaballo", SqlDbType.Int, 4)).Value = objCaballo.getIDCategoria();       //ESTO DEBERIA SER UN ID con inddice   A LA TABLA CABALLO

                        sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                        ControlLog.writeLog("[AgregarCaballoEnDB] SQL Query = " + sCommand.CommandText);

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[AgregarCaballoEnDB] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                        sCommand.CommandText = "SELECT IDENT_CURRENT('Caballos_HORSEJACK')";
                        sCommand.Parameters.Clear();

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                                if (sReader.Read())
                                {
                                    id = Convert.ToInt32(sReader[0].ToString());
                                }
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[AgregarCaballoEnDB] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                    }

                }
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[AgregarCaballoEnDB] Excepcion =" + ex.Message);
                throw ex;
            }

            return id;


        }


        internal bool UsuarioActivaPorPrimeraVez(string emailParam)
        {
            bool boolValue = false;
            try
            {
                using (SqlConnection sConn = new SqlConnection())
                {
                    sConn.ConnectionString = strConnectionString;

                    using (SqlCommand sCommand = new SqlCommand())
                    {
                        sCommand.Connection = sConn;
                        sCommand.CommandType = CommandType.Text;
                        sCommand.CommandText = "SELECT ActivarPrimeraVez FROM Clientes_HORSEJACK WHERE mail = '" + emailParam + "'";


                        ControlLog.writeLog("[UsuarioActivaPorPrimeraVez] SQL Query = " + sCommand.CommandText);

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                                if (sReader.Read())
                                {
                                    string value = sReader[0].ToString();
                                    ControlLog.writeLog("[UsuarioActivaPorPrimeraVez] Valor =" + value);
                                    boolValue = Convert.ToBoolean(value);
                                }
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[UsuarioActivaPorPrimeraVez] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                    }

                }
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[UsuarioActivaPorPrimeraVez] Excepcion =" + ex.Message);
                throw ex;
            }

            return boolValue;
        }

        internal bool ActivarImeiPorEmailUsuario(string emailParam, string imeiParam)
        {
            bool boolValue = false;
            try
            {
                using (SqlConnection sConn = new SqlConnection())
                {
                    sConn.ConnectionString = strConnectionString;

                    using (SqlCommand sCommand = new SqlCommand())
                    {
                        sCommand.Connection = sConn;
                        sCommand.CommandType = CommandType.Text;
                        sCommand.CommandText = "UPDATE Equipos_HORSEJACK " +
                                                "SET IMEI = '" + imeiParam + "'  " +
                                                " WHERE idEquipo = (SELECT idEquipo from Caballos_HORSEJACK with(nolock) WHERE idCliente = (SELECT idCliente FROM Clientes_HORSEJACK with(nolock) WHERE mail = '" + emailParam + "'))";

                        ControlLog.writeLog("[ActivarImeiPorEmailUsuario] SQL Query = " + sCommand.CommandText);

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                                boolValue = true;
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[ActivarImeiPorEmailUsuario] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                    }
                }

                using (SqlConnection sConn = new SqlConnection())
                {
                    sConn.ConnectionString = strConnectionString;

                    using (SqlCommand sCommand = new SqlCommand())
                    {
                        sCommand.Connection = sConn;
                        sCommand.CommandType = CommandType.Text;
                        sCommand.CommandText = "UPDATE Clientes_Horsejack " +
                                                "SET ActivarPrimeraVez = 0 " +
                                                " WHERE mail = '" + emailParam + "'";

                        ControlLog.writeLog("[ActivarImeiPorEmailUsuario] SQL Query = " + sCommand.CommandText);

                        try
                        {
                            sConn.Open();
                            using (SqlDataReader sReader = sCommand.ExecuteReader())
                            {
                                boolValue = true;
                            }
                            sConn.Close();
                        }
                        catch (Exception ex)
                        {
                            ControlLog.writeLog("[ActivarImeiPorEmailUsuario] Excepcion DB =" + ex.Message);
                            throw ex;

                        }

                    }
                }
            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[ActivarImeiPorEmailUsuario] Excepcion =" + ex.Message);
                throw ex;
            }

            return boolValue;
        }

        internal List<Cliente> ObtenerClientes()
        {
            List<Cliente> lc = new List<Cliente>();
            using (SqlConnection sConn = new SqlConnection())
            {
                sConn.ConnectionString = strConnectionString;

                using (SqlCommand sCommand = new SqlCommand())
                {
                    sCommand.Connection = sConn;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText = "SELECT * FROM Clientes_Horsejack";
                    ControlLog.writeLog("[ObtenerClientes] SQL Query = " + sCommand.CommandText);

                    try
                    {
                        sConn.Open();
                        using (SqlDataReader sReader = sCommand.ExecuteReader())
                        {
                            while (sReader.Read())
                            {
                                // idCliente	Usuario	Nombre	Apellido	Password	mail	LatitudCent	LongitudCent	TelefonoPrimario	ActivarPrimeraVez
                                Cliente newClient = new Cliente()
                                                              {
                                                                  intIdUsuario = Convert.ToInt32(sReader[0].ToString()),
                                                                  strUsuario = sReader[1].ToString(),
                                                                  strNombre = sReader[2].ToString(),
                                                                  strApelido = sReader[3].ToString(),
                                                                  strPassword = sReader[4].ToString(),
                                                                  strMail = sReader[5].ToString(),
                                                                  strLatitudCent = sReader[6].ToString(),
                                                                  strLongitudCent = sReader[7].ToString(),
                                                                  strPrimaryPhone = sReader[8].ToString(),
                                                                  ActivaPorPrimeraVez = (sReader[9].ToString() == "0" ? false : true)
                                                              };
                                lc.Add(newClient);
                            }
                        }
                        sConn.Close();
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[ObtenerClientes] Excepcion DB =" + ex.Message);
                        throw ex;

                    }

                }
            }



            return lc;
        }

        internal bool AgregarTransaccion(Transaccion transParam)
        {

            using (SqlConnection sConn = new SqlConnection())
            {
                sConn.ConnectionString = strConnectionString;

                using (SqlCommand sCommand = new SqlCommand())
                {
                    sCommand.Connection = sConn;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText = "INSERT INTO Transaccion (tra_fecha,tra_tipo,tra_additional_data_1,tra_additional_data_2) " +
                                            "VALUES(GETDATE(),'" + transParam.Tipo + "','" + transParam.InfoAdicional1 + "','" + transParam.InfoAdicional2 + "')";
                    ControlLog.writeLog("[AgregarTransaccion] SQL Query = " + sCommand.CommandText);

                    try
                    {
                        sConn.Open();
                        using (SqlDataReader sReader = sCommand.ExecuteReader())
                        {

                        }
                        sConn.Close();
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[AgregarTransaccion] Excepcion DB =" + ex.Message);
                        throw ex;

                    }

                }
            }



            return true;
        }

        internal List<Transaccion> ObtenerTransacciones()
        {

            List<Transaccion> lt = new List<Transaccion>();
            using (SqlConnection sConn = new SqlConnection())
            {
                sConn.ConnectionString = strConnectionString;

                using (SqlCommand sCommand = new SqlCommand())
                {
                    sCommand.Connection = sConn;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText = "SELECT * FROM Transaccion";
                    ControlLog.writeLog("[ObtenerTransacciones] SQL Query = " + sCommand.CommandText);

                    try
                    {
                        sConn.Open();
                        using (SqlDataReader sReader = sCommand.ExecuteReader())
                        {
                            while (sReader.Read())
                            {
                                
                                Transaccion newTrans = new Transaccion()
                                                              {
                                                                 Id = sReader[0].ToString(),
                                                                 Fecha = sReader[1].ToString(),
                                                                 Tipo = sReader[2].ToString(),
                                                                 InfoAdicional1 = sReader[3].ToString(),
                                                                 InfoAdicional2 = sReader[4].ToString()
                                                              };
                                lt.Add(newTrans);
                            }
                        }
                        sConn.Close();
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[ObtenerTransacciones] Excepcion DB =" + ex.Message);
                        throw ex;

                    }

                }
            }



            return lt;
        }

        public Trazabilidad ObtenerTrazabilidadHistorica(Caballo objCaballo)
        {
            Trazabilidad objCaminoRealizado = new Trazabilidad();
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();


            sCommand.Connection = sConn;
            sConn.ConnectionString = strConnectionString;

            sCommand.CommandType = CommandType.Text;
            //sCommand.CommandText = "SELECT TOP " + intCantidadDePasos.ToString() + " Latitud,Longitud,Fecha FROM Trazabilidad WHERE idCaballo=@idCaballo ORDER BY Fecha DESC ";
            sCommand.CommandText = "SELECT Latitud,Longitud,Fecha FROM TrazabilidadHistorica with(nolock) WHERE idCaballo=@idCaballo  ORDER BY Fecha DESC ";

            // ControlLog.writeLog("[obtenerCaminoCaballo] SELECT TOP " + intCantidadDePasos.ToString() + " Latitud,Longitud,Fecha FROM Trazabilidad with(nolock) WHERE idCaballo=" + objCaballo.strIDCaballo + " ORDER BY Fecha DESC ");
            ControlLog.writeLog("[ObtenerTrazabilidadHistorica] SELECT  Latitud,Longitud,Fecha FROM TrazabilidadHistorica with(nolock) WHERE idCaballo=" + objCaballo.strIDCaballo + "  ORDER BY Fecha DESC ");

            sCommand.Parameters.Add(new SqlParameter("@idCaballo", SqlDbType.VarChar, 20)).Value = objCaballo.strIDCaballo;
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;


            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    try
                    {
                        objCaminoRealizado.agregarCoordenadas(Convert.ToDouble(sReader.GetValue(0).ToString()), Convert.ToDouble(sReader.GetValue(1)));
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[ObtenerTrazabilidadHistorica] Excepcion  =" + ex.Message + " Valores: " + sReader.GetValue(0).ToString() + "-" + sReader.GetValue(1).ToString());
                    }
                }

                ControlLog.writeLog("[ObtenerTrazabilidadHistorica] Cantidad de pasos encontrados: " + objCaminoRealizado.obtenerCantidadDePasos());


            }
            catch (Exception ex)
            {
                ControlLog.writeLog("[ObtenerTrazabilidadHistorica] Excepcion DB =" + ex.Message);
                throw ex;

            }
            sConn.Close();

            return objCaminoRealizado;

        }

        /*
         Select cl.Usuario, ca.UltimaActualizacion from [Caballos_HORSEJACK] ca
INNER JOIN CLientes_HORSEJACK cl on ca.idCliente = cl.idCliente
ORDER by ca.UltimaActualizacion desc
         */
        internal List<string> UltimasActualizaciones()
        {
            List<string> ultimasAct = new List<string>();

            using (SqlConnection sConn = new SqlConnection())
            {
                sConn.ConnectionString = strConnectionString;

                using (SqlCommand sCommand = new SqlCommand())
                {
                    sCommand.Connection = sConn;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText =  " Select cl.Usuario, ca.UltimaActualizacion from [Caballos_HORSEJACK] ca "+
                                            " INNER JOIN CLientes_HORSEJACK cl on ca.idCliente = cl.idCliente "+
                                            " ORDER by ca.UltimaActualizacion desc ";

                    ControlLog.writeLog("[UltimasActualizaciones] SQL Query = " + sCommand.CommandText);

                    try
                    {
                        sConn.Open();
                        using (SqlDataReader sReader = sCommand.ExecuteReader())
                        {
                            while(sReader.Read())
                            {
                                ultimasAct.Add("Cliente: " + sReader[0].ToString() + " - Fecha: " + sReader[1].ToString());
                            }
                            
                        }
                        sConn.Close();
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[UltimasActualizaciones] Excepcion DB =" + ex.Message);
                        throw ex;

                    }

                }
            }



            return ultimasAct;
        }

        internal bool DeshabilitarCelado(int idCelado)
        {
            using (SqlConnection sConn = new SqlConnection())
            {
                sConn.ConnectionString = strConnectionString;

                using (SqlCommand sCommand = new SqlCommand())
                {
                    sCommand.Connection = sConn;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText = "UPDATE Caballos_HORSEJACK SET habilitado = 0 WHERE idCaballo = " + idCelado.ToString();

                    ControlLog.writeLog("[DeshabilitarCelado] SQL Query = " + sCommand.CommandText);

                    try
                    {
                        sConn.Open();
                        using (SqlDataReader sReader = sCommand.ExecuteReader())
                        {

                        }
                        sConn.Close();
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[DeshabilitarCelado] Excepcion DB =" + ex.Message);
                        throw ex;

                    }

                }
            }



            return true;
        }

        internal bool HabilitarCelado(int idCelado)
        {
            using (SqlConnection sConn = new SqlConnection())
            {
                sConn.ConnectionString = strConnectionString;

                using (SqlCommand sCommand = new SqlCommand())
                {
                    sCommand.Connection = sConn;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText = "UPDATE Caballos_HORSEJACK SET habilitado = 1 WHERE idCaballo = " + idCelado.ToString();

                    ControlLog.writeLog("[HabilitarCelado] SQL Query = " + sCommand.CommandText);

                    try
                    {
                        sConn.Open();
                        using (SqlDataReader sReader = sCommand.ExecuteReader())
                        {

                        }
                        sConn.Close();
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[HabilitarCelado] Excepcion DB =" + ex.Message);
                        throw ex;

                    }

                }
            }



            return true;
        }

        public bool CambiarPassword(Cliente objUsuario, string p)
        {
            bool boolReturn = false;
            using (SqlConnection sConn = new SqlConnection())
            {
                sConn.ConnectionString = strConnectionString;

                using (SqlCommand sCommand = new SqlCommand())
                {
                    sCommand.Connection = sConn;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText = "UPDATE Clientes_HORSEJACK SET password = '"+p+"' WHERE idCliente = " + objUsuario.intIdUsuario;

                    ControlLog.writeLog("[CambiarPassword] SQL Query = " + sCommand.CommandText);

                    try
                    {
                        sConn.Open();
                        using (SqlDataReader sReader = sCommand.ExecuteReader())
                        {
                            boolReturn = true;
                        }
                        sConn.Close();
                    }
                    catch (Exception ex)
                    {
                        ControlLog.writeLog("[CambiarPassword] Excepcion DB =" + ex.Message);
                        throw ex;

                    }

                }
            }

            return boolReturn;
        }
    }
}